'''※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
File Name: mysqlite3.py
Author: GID5564
Description: 内置数据库sqlite3操作
Version: 1.0
Created Time: 04/09/23-09:03:59
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※'''
  
#!/usr/bin/python
# -*- coding: UTF-8 -*-

import sqlite3
from sqlite3 import Error

#新建
#NEW_STATEMENT = "create table if not exists user(id varchar(128))"
#连接
#添加
#更新/修改
#删除
#查询

'''写一个类打包成库，通用于储存信息的sqlite'''
'''函数返回值可优化'''
'''使用：使用'''
'''说明：1、单例模式连接数据库：避免数据库connect过多导致数据库down
        2、根据数据库增删查改性能对比，统一使用execute进行常规数据库操作
        3、且不做try操作：1、影响性能 2、若报错，外部调用无法确定问题所在，'''

class LiteDb(object):
    _instance = None

    def __new__(cls, *args, **kw):
        if cls._instance is None:
            cls._instance = object.__new__(cls)
        return cls._instance

    def openDb(self, dbname):
        self.dbname = dbname
        try:
            self.conn = sqlite3.connect(self.dbname)
            if self.conn is not None:    
                self.cursor = self.conn.cursor()
        except Error as e:
            print(e)

    def closeDb(self):
        '''
        关闭数据库
        :return:
        '''
        if self.cursor is not None: 
            self.cursor.close()
        if self.conn is not None: 
            self.conn.close()

    def createTables(self, sql):
        '''
        example：'create table userinfo(name text, email text)'
        :return: result=[1,None]  
        '''
        self.cursor.execute(sql)
        self.conn.commit()
        result = [1, None]
        return result

    def dropTables(self, sql):
        '''
        example:'drop table userinfo'
        :param sql:
        :return:result=[1,None]
        '''
        self.cursor.execute(sql)
        self.conn.commit()
        result = [1, None]
        return result

    def executeSql(self, sql, value=None):
        '''
        执行单个sql语句，只需要传入sql语句和值便可
        :param sql:'insert into user(name,password,number,status) values(?,?,?,?)'
                    'delete from user where name=?'
                    'updata user set status=? where name=?'
                    'select * from user where id=%s'
        :param value:[(123456,123456,123456,123456),(123,123,123,123)]
                value:'123456'
                value:(123,123)
        :return:result=[1,None]
        '''

        '''增、删、查、改'''
        if isinstance(value,list) and isinstance(value[0],(list,tuple)):
            for valu in value:
                self.cursor.execute(sql, valu)
            else:
                self.conn.commit()
                result = [1, self.cursor.fetchall()]
        else:
            '''执行单条语句：字符串、整型、数组'''
            if value:
                self.cursor.execute(sql, value)
            else:
                self.cursor.execute(sql)
            self.conn.commit()
            result = [1, self.cursor.fetchall()]
        return result





#测试用例

'''对于二次封装的数据库进行测试'''
'''增删查改'''

#用例
'''
select name from sqlite_master where type='table
select * from user
'select * from user where id = %s'%7
select * from user where id = ? , 7
select * from user where id=? or id=?, ['7','8']

insert  into user(id) values(7)
'insert  into user(id) values(%s)'%7
'insert  into user(id) values(?)',[('10',),('11',)]

delete from user where id=7
'delete from user where id=%s'%7
'delete from user where id=?',[('10',),('11',)]

update user set id=7 where id=11
'update user set id=%s where id=%s'%(10,20)
'update user set id=? where id=?',[('21','11'),('20','10')]'''


'''
db=LiteDb()
db.openDb('user.db')

def close():
    db.closeDb()

def createTables():
    result=db.createTables('create table if not exists user(id varchar(128))')

def executeSQL():
    '''增删查改'''
    result = db.executeSql('insert  into user(id) values(?)',('99',))
    result = db.executeSql('select * from user ')

executeSQL()
close()
'''



























